﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class QUYDINHSACHDAO
    {
        public int tinhtienphat(int songayquahan)
        {
            int tien;
            string sql;
            SqlConnection con = DATAPROVIDER.ConnectSQL();
            if (songayquahan < 6)
            {
                sql = "SELECT GiaTri FROM QUY_DINH_SACH WHERE MaQuyDinh=1";
            }
            else
            {
                if (songayquahan > 7 && songayquahan < 15)
                    sql = "SELECT GiaTri FROM QUY_DINH_SACH WHERE MaQuyDinh=2";
                else
                {
                    if(songayquahan >15 && songayquahan<31)
                        sql = "SELECT GiaTri FROM QUY_DINH_SACH WHERE MaQuyDinh=3";
                    else sql = "SELECT GiaTri FROM QUY_DINH_SACH WHERE MaQuyDinh=4";
                }

            }
            SqlCommand cmd = new SqlCommand(sql, con);
            object temp = cmd.ExecuteScalar();
            tien = Convert.ToInt16(temp.ToString());
            tien = tien * songayquahan;
            return tien;
        }

        public int update(DataTable datatable, string sqlquery)
        {
            int effrow = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sqlquery;
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            cmd.Connection = conn;
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmd);
            SqlCommandBuilder sqlcomandbuilder = new SqlCommandBuilder(sqldataadapter);
            effrow = sqldataadapter.Update(datatable);
            sqldataadapter.Dispose();

            conn.Close();
            conn.Dispose();

            return effrow;
        }

        public int themquydinh(QUYDINHSACHDTO qdsDTO)
        {
            SqlConnection con = DATAPROVIDER.ConnectSQL();
            string sql = "INSERT INTO QUY_DINH_SACH VALUES(@TQD,@GT)";
            SqlCommand com = new SqlCommand(sql, con);

            SqlParameter para = new SqlParameter("@TQD", System.Data.SqlDbType.NVarChar, 50);
            para.Value = qdsDTO.Tenquydinh;
            com.Parameters.Add(para);

            para = new SqlParameter("@GT", System.Data.SqlDbType.Int);
            para.Value = qdsDTO.Giatri;
            com.Parameters.Add(para);

            int kq = com.ExecuteNonQuery();
            return kq;
            
        }

        public DataTable Loadbangquydinhsach()
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM QUY_DINH_SACH";
            SqlDataAdapter sqldata = new SqlDataAdapter(sql, conn);
            DataTable temp = new DataTable();
            sqldata.Fill(temp);
            conn.Dispose();
            sqldata.Dispose();
            return temp;
        }
    }
}
